/****** Object:  StoredProcedure [dbo].[AuthZ_spGetUserByAlias]    Script Date: 08/09/2009 18:20:15 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetUserByAlias]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetUserByAlias]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetUserByAlias]    Script Date: 08/09/2009 18:20:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<revans>
-- Create date: <6-24-2009>
-- Description:	<Retrieves a user for the given alias.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetUserByAlias]

	@alias as nvarchar(50) = ''

AS
BEGIN
SET NOCOUNT ON

		BEGIN TRY
				
		Declare @roleName nvarchar(500)
		Declare @personID int
		Declare @fullname nvarchar(50)
		Declare @email nvarchar(50)
		Declare @display nvarchar(50)
		Declare @pnumber int		
		
		SELECT @personID = PersonID
		, @fullname = FullName
		, @email = EMailName
		, @display = DisplayName
		, @pnumber = PersonnelNumber		
		FROM authz_vwUserRole 
		WHERE Alias = @alias
		
				
		Set @roleName = N''
		Select @roleName = @roleName + R.RoleName + ', '  from dbo.AuthZUserRole TEMP
		INNER JOIN Role R ON R.RoleID = TEMP.RoleID 
		INNER JOIN AuthZUser AU ON AU.sAMAccountName = @alias and TEMP.AuthZUserID = AU.AuthZUserID 
		Set @roleName = Left(@roleName,Len(@roleName)-1)
		
		select @personID,@fullname,@email,@display,@pnumber,@alias,@roleName
		
	END TRY
	BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
	END CATCH
END

GO
